package com.lq.dept.dao.impl;

import com.lq.dept.dao.DeptDao;
import com.lq.dept.domain.Dept;
import com.lq.dept.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//Impl：实现类   alt+enter
public class DeptDaoImpl implements DeptDao {

    private Connection conn;
    private PreparedStatement pst;
    private ResultSet rs;

    @Override
    public boolean delDept(int deptno) {
        String sql="delete from dept where deptno = ?";
        try {
            conn= DBUtil.getConn();
            pst=conn.prepareStatement(sql);
            pst.setInt(1,deptno);
            pst.executeUpdate();
            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(conn,pst,rs);
        }
        return false;
    }

    @Override
    public Dept queryDept(int deptno) {
        String sql="select * from dept where deptno = ?";
        try {
            conn=DBUtil.getConn();
            pst=conn.prepareStatement(sql);
            pst.setInt(1,deptno);
            rs=pst.executeQuery();
            if(rs.next()){
                Dept dept=new Dept();
                dept.setDeptno(rs.getInt("deptno"));
                dept.setDname(rs.getString("dname"));
                dept.setLoc(rs.getString("loc"));
                return dept;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(conn,pst,rs);
        }
        return null;
    }

    @Override
    public List<Dept> queryAllDept() {
        List<Dept> dlist=new ArrayList<>();
        String sql="select * from dept";
        try {
            conn=DBUtil.getConn();
            pst=conn.prepareStatement(sql);
            rs=pst.executeQuery();
            //遍历rs====>部门信息===>Dept对象===>dlist
            while(rs.next()){
                Dept dept=new Dept();
                dept.setDeptno(rs.getInt("deptno"));
                dept.setDname(rs.getString("dname"));
                dept.setLoc(rs.getString("loc"));
                dlist.add(dept);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(conn,pst,rs);
        }
        return dlist;
    }

    @Override
    public boolean updateDept(Dept dept) {
        String sql="update dept set dname = ? , loc = ? where deptno = ?";
        try {
            conn= DBUtil.getConn();//获得数据库连接
            pst=conn.prepareStatement(sql);//准备执行sql
            pst.setString(1,dept.getDname());
            pst.setString(2,dept.getLoc());
            pst.setInt(3,dept.getDeptno());
            pst.executeUpdate();//执行sql
            return true;//true，添加成功
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(conn,pst,rs);//关闭资源
        }
        return false;//false 添加失败
    }

    @Override
    public boolean addDept(Dept dept) {
        String sql="insert into dept values(?,?,?)";
        try {
            conn= DBUtil.getConn();//获得数据库连接
            pst=conn.prepareStatement(sql);//准备执行sql
            pst.setInt(1,dept.getDeptno());
            pst.setString(2,dept.getDname());
            pst.setString(3,dept.getLoc());
            pst.executeUpdate();//执行sql
            return true;//true，添加成功
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(conn,pst,rs);//关闭资源
        }
        return false;//false 添加失败
    }

    public static void main(String[] args) {
        DeptDao dao=new DeptDaoImpl();
        System.out.println(dao.queryAllDept());
        //System.out.println(dao.queryDept(200));//有
        //System.out.println(dao.queryDept(250));//null
        // Dept dept=new Dept(5566,"唱跳rap","美国");
        // boolean b=dao.addDept(dept);
        // System.out.println(b);
        // boolean b= dao.delDept(100);
        // System.out.println(b);
    }
}
